﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_Flow_GetDocumentStatusByStaff]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_Flow_GetDocumentStatusByStaff];
GO
CREATE PROCEDURE [dbo].[sproc_Flow_GetDocumentStatusByStaff]
    @StaffName nvarchar(300),
    @DocID int
/*

============================================================
功能:    得到个人对文档的操作状态
参数:
    @StaffName nvarchar    :    操作人
    @DocID int        :    文档ID
注释：
    --------------------
    操作状态        意义
    --------------------
    0        签收
    1        一般待批
    2        撤回
    3        查看
    4        会签待批

    --------------------
    文档状态        意义
    --------------------
    0        未签
    1        待批
    2        会签中
    3        完成
    4        拒绝

============================================================

*/
AS
DECLARE @Status int
DECLARE @staff_id int

SELECT @staff_id = staff_id 
    FROM uds_staff
    WHERE staff_name = @StaffName

IF EXISTS(SELECT 1 FROM uds_flow_status WHERE staff_id = @staff_id AND doc_id = @DocID)
BEGIN
    SELECT @Status = Status FROM uds_flow_status WHERE staff_id = @staff_id AND doc_id = @DocID
    --对于未签文档，操作是签收
    If @Status = 0
    BEGIN
        --判断该文档当前环节是否会签
        IF EXISTS(SELECT 1 FROM uds_flow_document WHERE (doc_status =2 OR doc_status =0) and doc_id = @DocID )            
            SET @Status =0
        ELSE
            SET @Status =3
    END
    --对于已签收文档，操作是待批
    If @Status = 1
    BEGIN
        --判断该文档当前环节是否会签
        IF EXISTS(SELECT 1 FROM uds_flow_document WHERE (doc_status =2 OR doc_status =0) and doc_id = @DocID )            
            SET @Status =4
        ELSE
            SET @Status =1
    END
    --对于已批阅的文档，操作是查看
    If @Status = 2
        SET @Status =3
END
ELSE
    SET @Status = 3
--对于没有人签收和没人批阅的和撰稿人是自己的操作状态为撤回
--IF NOT EXISTS(SELECT 1 FROM uds_flow_status WHERE doc_id = @DocID and status>0) 
--and NOT EXISTS(SELECT 1 FROM uds_flow_postil WHERE doc_id = @DocID)
--and     EXISTS(SELECT 1 FROM uds_flow_document    WHERE Doc_Builder_ID = @staff_id and doc_id = @DocID)        
--    SET @Status = 2

PRINT '对于' +@StaffName +'的当前状态'+CONVERT(nvarchar,@Status)
RETURN @Status